// script.js
// 初始化SQL编辑器
const editor = CodeMirror(document.getElementById('editor'), {
    mode: 'text/x-sql',
    theme: 'dracula',
    lineNumbers: true,
    matchBrackets: true,
    autoCloseBrackets: true,
    tabSize: 2,
    indentUnit: 4,
    extraKeys: {"Ctrl-Space": "autocomplete"},
    hintOptions: {tables: {
        employees: ["id", "name", "email", "salary", "hire_date", "department_id"],
        departments: ["id", "department_name", "location", "budget"],
        projects: ["id", "project_name", "start_date", "end_date", "manager_id"]
    }},
    placeholder: '在此输入您的SQL语句...',
    value: `-- 欢迎使用SQL编辑器\n-- 尝试编写SQL并点击"执行SQL"按钮\n\nSELECT * FROM employees ORDER BY hire_date DESC;\n\n-- 点击查询结果中的列名可以对该列进行排序`
});

// 获取DOM元素
const runBtn = document.getElementById('runBtn');
const formatBtn = document.getElementById('formatBtn');
const clearBtn = document.getElementById('clearBtn');
const themeBtn = document.getElementById('themeBtn');
const output = document.getElementById('output');
const status = document.getElementById('status');
const executionTime = document.getElementById('execution-time');
const exampleCards = document.querySelectorAll('.example-card');

// 存储SQL.js数据库实例
let db;

// 存储当前查询结果
let currentResult = null;
let currentSort = { column: null, direction: 'asc' };

// 分页设置
let currentPage = 1;
const rowsPerPage = 10;

// 设置初始主题
let currentTheme = 'dracula';

// 运行按钮事件
runBtn.addEventListener('click', executeSQL);

// 格式化按钮事件
formatBtn.addEventListener('click', formatSQL);

// 清空按钮事件
clearBtn.addEventListener('click', () => {
    editor.setValue('');
    output.innerHTML = '<div class="info">执行SQL语句后，结果将显示在此区域</div>';
    status.textContent = '编辑器已清空';
    currentResult = null;
    currentSort = { column: null, direction: 'asc' };
    currentPage = 1;
});

// 主题切换按钮事件
themeBtn.addEventListener('click', () => {
    currentTheme = currentTheme === 'dracula' ? 'default' : 'dracula';
    editor.setOption('theme', currentTheme);
});

// 示例代码点击事件
exampleCards.forEach(card => {
    card.addEventListener('click', () => {
        const exampleType = card.getAttribute('data-example');
        loadExample(exampleType);
    });
});

// 初始化SQL.js数据库
async function initDatabase() {
    try {
        // 加载SQL.js
        const SQL = await initSqlJs({
            locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${file}`
        });

        // 创建新数据库
        db = new SQL.Database();

        // 创建表结构并插入示例数据
        createSampleDatabase();

        status.textContent = '数据库已初始化';
    } catch (err) {
        showError('数据库初始化失败: ' + err.message);
    }
}

// 创建示例数据库结构
function createSampleDatabase() {
    // 创建表
    db.run(`CREATE TABLE departments (
        id INTEGER PRIMARY KEY,
        department_name TEXT NOT NULL,
        location TEXT,
        budget REAL
    );`);

    db.run(`CREATE TABLE employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT,
        salary REAL,
        hire_date DATE,
        department_id INTEGER,
        FOREIGN KEY (department_id) REFERENCES departments(id)
    );`);

    db.run(`CREATE TABLE projects (
        id INTEGER PRIMARY KEY,
        project_name TEXT NOT NULL,
        start_date DATE,
        end_date DATE,
        manager_id INTEGER,
        FOREIGN KEY (manager_id) REFERENCES employees(id)
    );`);

    // 插入部门数据
    db.run(`INSERT INTO departments (department_name, location, budget) VALUES
        ('技术部', '北京', 500000),
        ('市场部', '上海', 300000),
        ('人力资源', '广州', 200000),
        ('财务部', '深圳', 400000);`);

    // 插入员工数据
    db.run(`INSERT INTO employees (name, email, salary, hire_date, department_id) VALUES
        ('张三', 'zhangsan@example.com', 15000, '2020-01-15', 1),
        ('李四', 'lisi@example.com', 12000, '2019-05-22', 1),
        ('王五', 'wangwu@example.com', 18000, '2018-11-03', 1),
        ('赵六', 'zhaoliu@example.com', 10000, '2021-02-18', 2),
        ('钱七', 'qianqi@example.com', 9000, '2020-07-30', 2),
        ('孙八', 'sunba@example.com', 11000, '2019-03-12', 3),
        ('周九', 'zhoujiu@example.com', 13000, '2021-08-05', 4),
        ('吴十', 'wushi@example.com', 16000, '2017-12-20', 4),
        ('郑十一', 'zheng11@example.com', 14000, '2022-04-10', 1),
        ('王十二', 'wang12@example.com', 17000, '2019-09-15', 3),
        ('刘十三', 'liu13@example.com', 12500, '2020-11-22', 2),
        ('陈十四', 'chen14@example.com', 15500, '2021-06-30', 4),
        ('杨十五', 'yang15@example.com', 13500, '2018-07-18', 1);`);

    // 插入项目数据
    db.run(`INSERT INTO projects (project_name, start_date, end_date, manager_id) VALUES
        ('ERP系统升级', '2023-01-10', '2023-06-30', 1),
        ('移动应用开发', '2023-02-15', '2023-08-15', 2),
        ('市场推广活动', '2023-03-01', '2023-05-31', 4),
        ('财务系统优化', '2023-04-01', '2023-09-30', 7),
        ('员工培训计划', '2023-05-15', '2023-10-15', 6),
        ('新产品研发', '2023-06-01', '2023-12-31', 3);`);
}

// 执行SQL函数
function executeSQL() {
    if (!db) {
        showError('数据库尚未初始化');
        return;
    }

    // 清空输出区域
    output.innerHTML = '';
    status.textContent = '执行中...';
    currentPage = 1;
    currentSort = { column: null, direction: 'asc' };

    try {
        const startTime = performance.now();

        // 获取SQL代码
        const sql = editor.getValue();

        // 执行SQL
        const result = db.exec(sql);
        const endTime = performance.now();
        const timeTaken = (endTime - startTime).toFixed(2);

        executionTime.textContent = `执行时间: ${timeTaken}ms`;

        if (result.length === 0) {
            const rowsAffected = db.getRowsModified();
            if (rowsAffected > 0) {
                output.innerHTML = `<div class="success">执行成功！影响行数: ${rowsAffected}</div>`;
                status.textContent = '执行成功';
            } else {
                output.innerHTML = '<div class="success">命令执行成功，但没有返回结果</div>';
                status.textContent = '执行成功';
            }
            return;
        }

        // 保存当前结果
        currentResult = result[0];

        // 显示结果
        renderResultTable(currentResult);

        status.textContent = '执行成功';

    } catch (err) {
        showError('SQL执行错误: ' + err.message);
        status.textContent = '执行出错';
    }
}

// 渲染结果表格（带排序功能）
function renderResultTable(resultData) {
    output.innerHTML = '';

    if (!resultData) {
        showError('没有可显示的结果');
        return;
    }

    // 创建表格
    const table = document.createElement('table');
    table.className = 'result-table';

    // 创建表头
    const thead = document.createElement('thead');
    const headerRow = document.createElement('tr');

    resultData.columns.forEach((col, index) => {
        const th = document.createElement('th');
        th.textContent = col;
        th.dataset.columnIndex = index;

        // 添加排序指示器
        if (currentSort.column === index) {
            th.classList.add(currentSort.direction === 'asc' ? 'sorted-asc' : 'sorted-desc');
        }

        // 添加点击事件
        th.addEventListener('click', () => {
            sortTable(index);
        });

        headerRow.appendChild(th);
    });

    thead.appendChild(headerRow);
    table.appendChild(thead);

    // 创建表体
    const tbody = document.createElement('tbody');

    // 获取当前页的数据
    const startIndex = (currentPage - 1) * rowsPerPage;
    const endIndex = Math.min(startIndex + rowsPerPage, resultData.values.length);
    const pageData = resultData.values.slice(startIndex, endIndex);

    // 填充数据
    pageData.forEach(row => {
        const tr = document.createElement('tr');

        row.forEach(cell => {
            const td = document.createElement('td');
            td.textContent = cell;
            tr.appendChild(td);
        });

        tbody.appendChild(tr);
    });

    table.appendChild(tbody);

    // 显示结果信息
    const resultInfo = document.createElement('div');
    resultInfo.className = 'success';
    resultInfo.textContent = `查询成功！返回 ${resultData.values.length} 行数据`;

    output.appendChild(resultInfo);
    output.appendChild(table);

    // 添加分页控件
    if (resultData.values.length > rowsPerPage) {
        addPaginationControls(resultData.values.length);
    }

    // 添加结果信息
    const resultsInfo = document.createElement('div');
    resultsInfo.className = 'results-info';
    resultsInfo.innerHTML = `
        <span>显示 ${startIndex + 1}-${endIndex} 行，共 ${resultData.values.length} 行</span>
        <span>点击列名可排序</span>
    `;

    output.appendChild(resultsInfo);
}

// 排序表格
function sortTable(columnIndex) {
    if (!currentResult) return;

    // 判断排序方向
    let direction = 'asc';
    if (currentSort.column === columnIndex) {
        direction = currentSort.direction === 'asc' ? 'desc' : 'asc';
    }

    // 更新排序状态
    currentSort = {
        column: columnIndex,
        direction: direction
    };

    // 对数据进行排序
    currentResult.values.sort((a, b) => {
        const valA = a[columnIndex];
        const valB = b[columnIndex];

        // 尝试转换为数字比较
        const numA = parseFloat(valA);
        const numB = parseFloat(valB);

        if (!isNaN(numA) && !isNaN(numB)) {
            return direction === 'asc' ? numA - numB : numB - numA;
        }

        // 尝试转换为日期比较
        const dateA = new Date(valA);
        const dateB = new Date(valB);

        if (!isNaN(dateA) && !isNaN(dateB)) {
            return direction === 'asc' ? dateA - dateB : dateB - dateA;
        }

        // 默认字符串比较
        return direction === 'asc'
            ? String(valA).localeCompare(String(valB))
            : String(valB).localeCompare(String(valA));
    });

    // 重新渲染表格
    renderResultTable(currentResult);
}

// 添加分页控件
function addPaginationControls(totalRows) {
    const totalPages = Math.ceil(totalRows / rowsPerPage);

    const pagination = document.createElement('div');
    pagination.className = 'pagination';

    // 上一页按钮
    const prevButton = document.createElement('button');
    prevButton.innerHTML = '<i class="fas fa-chevron-left"></i>';
    prevButton.disabled = currentPage === 1;
    prevButton.addEventListener('click', () => {
        if (currentPage > 1) {
            currentPage--;
            renderResultTable(currentResult);
        }
    });
    pagination.appendChild(prevButton);

    // 页码按钮
    const startPage = Math.max(1, currentPage - 2);
    const endPage = Math.min(totalPages, currentPage + 2);

    for (let i = startPage; i <= endPage; i++) {
        const pageButton = document.createElement('button');
        pageButton.textContent = i;
        pageButton.classList.toggle('active', i === currentPage);
        pageButton.addEventListener('click', () => {
            currentPage = i;
            renderResultTable(currentResult);
        });
        pagination.appendChild(pageButton);
    }

    // 下一页按钮
    const nextButton = document.createElement('button');
    nextButton.innerHTML = '<i class="fas fa-chevron-right"></i>';
    nextButton.disabled = currentPage === totalPages;
    nextButton.addEventListener('click', () => {
        if (currentPage < totalPages) {
            currentPage++;
            renderResultTable(currentResult);
        }
    });
    pagination.appendChild(nextButton);

    output.appendChild(pagination);
}

// 格式化SQL函数
function formatSQL() {
    const sql = editor.getValue();

    // 简单格式化 - 实际项目中应使用更复杂的格式化工具
    let formattedSQL = sql
        .replace(/\b(SELECT|FROM|WHERE|JOIN|INNER|LEFT|RIGHT|OUTER|ON|GROUP BY|HAVING|ORDER BY|INSERT INTO|UPDATE|DELETE FROM|CREATE TABLE|ALTER TABLE|DROP TABLE|VALUES|SET)\b/gi, '\n$1')
        .replace(/,/g, ',\n  ')
        .replace(/\(/g, '(\n  ')
        .replace(/\)/g, '\n)');

    editor.setValue(formattedSQL);
    status.textContent = 'SQL已格式化';
}

// 显示错误信息
function showError(message) {
    const errorDiv = document.createElement('div');
    errorDiv.className = 'error';
    errorDiv.textContent = message;
    output.appendChild(errorDiv);
}

// 加载示例代码
function loadExample(type) {
    let exampleSQL = '';

    switch (type) {
        case "select":
            exampleSQL = `-- 基本SELECT查询
SELECT id, name, salary, hire_date
FROM employees
WHERE salary > 10000
ORDER BY hire_date DESC;`;
            break;

        case "join":
            exampleSQL = `-- JOIN连接查询
SELECT
    e.id AS employee_id,
    e.name AS employee_name,
    e.salary,
    d.department_name,
    d.location
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.location = '北京'
ORDER BY e.salary DESC;`;
            break;

        case "aggregate":
            exampleSQL = `-- 聚合函数查询
SELECT
    d.department_name,
    COUNT(e.id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name
HAVING avg_salary > 12000
ORDER BY avg_salary DESC;`;
            break;
    }

    editor.setValue(exampleSQL);
    status.textContent = `已加载"${type}"示例SQL`;
}

// 初始化数据库
initDatabase();